package pandabay2.DAO;

import java.io.File;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;

import javax.imageio.ImageIO;

import pandabay2.domain.Auction;
import pandabay2.service.ConnectionManager;

public class AuctionDAO {
	
	static Connection currentCon = null;
	static ResultSet rs = null;
	
	public static List<Auction> findAll(String category){
		System.out.println("in the AuctionDAO class - find all");
		String searchQuery;
		List<Auction> auctions = new ArrayList<Auction>();
		Auction auction = null;
		Statement stmt = null;
		if(category.equals("Brettspiel") || category.equals("Stofftier") || category.equals("Kinderbuch"))
			searchQuery = "SELECT * FROM auction where category ='" + category + "' AND buyer = 0";
		else
			searchQuery = "SELECT * FROM auction where buyer = 0";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			while(rs.next()) {
				auction = new Auction();
				auction.setAuction_id(rs.getInt("auction_id"));
				int auction_id = rs.getInt("auction_id");
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setBuyNow(rs.getInt("buyNow"));
				/* read 
				byte[] data;
				try {
					Blob blob = rs.getBlob("image");
		            data = blob.getBytes(1, (int) blob.length());
				} catch(Exception ex) {
					data = rs.getBytes("image");
				}
				InputStream in = new ByteArrayInputStream(data);
				BufferedImage bufferedImage = ImageIO.read(in);
				String path = "/images/" + auction_id + ".jpg";
				ImageIO.write(bufferedImage, "jpg", new File(path));
				auction.setPath(path); */
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				//auction.setBids(rs.getString(3));
				auctions.add(auction);
			}
		} catch (Exception ex) {
			System.out.println("Read auctions failed: An Exception has occurred! " + ex);
		} 
		return auctions;
	}
	
	public static List<Auction> findString(String searchString, String category){
		String searchQuery;
		List<Auction> auctions = new ArrayList<Auction>();
		Auction auction = null;
		Statement stmt = null;
		if(category.equals("Brettspiel") || category.equals("Stofftier") || category.equals("Kinderbuch"))
			searchQuery = "SELECT * FROM auction where category ='" + category + "' AND (description LIKE '%" + searchString + "%' OR name LIKE '%" + searchString + "%')";
		else
			searchQuery = "SELECT * FROM auction where description LIKE '%" + searchString + "%' OR name LIKE '%" + searchString + "%'";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			while(rs.next()) {
				auction = new Auction();
				auction.setAuction_id(rs.getInt("auction_id"));
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setImage(rs.getBytes("image"));
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				auction.setBuyNow(rs.getInt("buyNow"));
				//auction.setBids(rs.getString(3));
				auctions.add(auction);
			}
		} catch (Exception ex) {
			System.out.println("Read auctions failed: An Exception has occurred! " + ex);
		} 
		return auctions;
	}
	
	public static List<Auction> findId(int user_id){
		String searchQuery;
		List<Auction> auctions = new ArrayList<Auction>();
		Auction auction = null;
		Statement stmt = null;
		searchQuery = "SELECT * FROM auction WHERE seller = " + user_id;
		
		if(user_id == 0) return auctions;
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			while(rs.next()) {
				auction = new Auction();
				auction.setBuyer(rs.getInt("buyer"));
				auction.setAuction_id(rs.getInt("auction_id"));
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setCategory("all");
				auction.setImage(rs.getBytes("image"));
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				auction.setBuyNow(rs.getInt("buyNow"));
				//auction.setBids(rs.getString(3));
				auctions.add(auction);
			}
		} catch (Exception ex) {
			System.out.println("Read auctions (id) failed: An Exception has occurred! " + ex);
		} 
		return auctions;
	}
	
	public static List<Auction> findIdBuyer(int user_id){
		String searchQuery;
		List<Auction> auctions = new ArrayList<Auction>();
		Auction auction = null;
		Statement stmt = null;
		searchQuery = "SELECT * FROM auction WHERE buyer = " + user_id;
		
		if(user_id == 0) return auctions;
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			while(rs.next()) {
				auction = new Auction();
				auction.setBuyer(rs.getInt("buyer"));
				auction.setAuction_id(rs.getInt("auction_id"));
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setCategory("all");
				auction.setImage(rs.getBytes("image"));
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				auction.setBuyNow(rs.getInt("buyNow"));
				//auction.setBids(rs.getString(3));
				auctions.add(auction);
			}
		} catch (Exception ex) {
			System.out.println("Read auctions (id) failed: An Exception has occurred! " + ex);
		} 
		return auctions;
	}
	
	public static List<Auction> extFindString(String searchString, String category, int min, int max){
		String searchQuery;
		List<Auction> auctions = new ArrayList<Auction>();
		Auction auction = null;
		Statement stmt = null;
		if(category.equals("Brettspiel") || category.equals("Stofftier") || category.equals("Kinderbuch"))
			searchQuery = "SELECT * FROM auction where buyer = 0 AND category ='" + category + "' AND (description LIKE '%" + searchString + "%' OR name LIKE '%" + searchString + "%') AND (startPrice BETWEEN '" + min + "' AND '" + max + "')";
		else
			searchQuery = "SELECT * FROM auction where buyer = 0 AND (startPrice BETWEEN '" + min + "' AND '" + max + "') AND (description LIKE '%" + searchString + "%' OR name LIKE '%" + searchString + "%')";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			while(rs.next()) {
				auction = new Auction();
				auction.setAuction_id(rs.getInt("auction_id"));
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setImage(rs.getBytes("image"));
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				auction.setBuyNow(rs.getInt("buyNow"));
				//auction.setBids(rs.getString(3));
				auctions.add(auction);
			}
		} catch (Exception ex) {
			System.out.println("Read auctions failed: An Exception has occurred! " + ex);
		} 
		return auctions;
	}
	
	public static Auction readAuction(int auction_id){
		Auction auction = null;
		Statement stmt = null;
		String searchQuery = "SELECT * FROM auction where auction_id='" + auction_id + "'";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			boolean auctionExists = rs.next();
			
			if (!auctionExists) {
				System.out.println("Auction doesn't exist.");
			} else if (auctionExists) {
				auction = new Auction();
				auction.setAuction_id(rs.getInt("auction_id"));
				auction.setName(rs.getString("name"));
				auction.setDescription(rs.getString("description"));
				auction.setStartPrice(rs.getDouble("startPrice"));
				auction.setSeller(rs.getInt("seller"));
				auction.setHighestBidder(rs.getInt("highestBidder"));
				auction.setCategory(rs.getString("category"));
				auction.setImage(rs.getBytes("image"));
				auction.setStartDate(rs.getString("startDate"));
				auction.setEndDate(rs.getString("endDate"));
				auction.setHighestBid(rs.getInt("highestBid"));
				auction.setBuyer(rs.getInt("buyer"));
				auction.setBuyNow(rs.getInt("buyNow"));
			}	
			
		} catch (Exception ex) {
			System.out.println("Read auction failed: An Exception has occurred! " + ex);
		} 
		return auction;
	}
	
	public static boolean saveAuction(Auction auction){
		boolean valid = false;
		int buyer = 0;
		String name = auction.getName();
		String description = auction.getDescription();
		Double startPrice = auction.getStartPrice();
		int seller = auction.getSeller();
		int highestBidder = auction.getHighestBidder();
		String category = auction.getCategory();
		String startDate = auction.getStartDate();
		String endDate = auction.getEndDate();
		byte[] image = auction.getImage();
		int buyNow = auction.isBuyNow();
		
//		System.out.println(startDate);
//		System.out.println(endDate);
		
		Statement stmt = null;
		String saveQuery = "INSERT INTO auction (name, description, startPrice, seller, highestBidder, category, startDate, endDate, buyer, image, buyNow) VALUES ('" + name + "','" + description + "'," + startPrice + "," + seller + "," + highestBidder + ",'" + category + "','" + startDate + "','" + endDate + "','" + buyer + "','" + image + "'," + buyNow + ")";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.executeUpdate(saveQuery);
			valid = true;
		} catch (Exception ex) {
			System.out.println("Save auction failed: An Exception has occurred! " + ex);
		} 
		return valid;
	}
	
	public static void setNewBid(int auction_id, double newBid) {
		
		Statement stmt = null;
		String updateQuery = "UPDATE auction SET highestBid = "+ newBid +" WHERE auction_id = " + auction_id + "";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.executeUpdate(updateQuery);
		} catch (Exception ex) {
			System.out.println("Writing new bid failed: An Exception has occurred! " + ex);
		} 
	}
	
	public static void setBuyer(int auction_id, int buyer) {
		Statement stmt = null;
		String updateQuery = "UPDATE auction SET buyer = "+ buyer +" WHERE auction_id = " + auction_id + "";
		
		try {
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.executeUpdate(updateQuery);
		} catch (Exception ex) {
			System.out.println("Writing new buyer failed: An Exception has occurred! " + ex);
		} 
	}

	public static void isSold(Auction auction) {
//		Statement stmt = null;
//		String updateQuery = "UPDATE auction SET buyer = "+ buyer +" WHERE auction_id = " + auction + "";
//		
//		try {
//			currentCon = ConnectionManager.getConnection();
//			stmt=currentCon.createStatement();
//			stmt.executeUpdate(updateQuery);
//		} catch (Exception ex) {
//			System.out.println("Writing new buyer failed: An Exception has occurred! " + ex);
//		} 
//		return false;
	}
}
